package data

import (
	"commerce/common"
	"commerce/model"
	"commerce/vo"
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"
)

func PageGoods(pageNo, pageSize int, name string) (*common.Page, error) {

	whereSql := composeGoodsSearchQuerySql(name)

	row := common.DB.QueryRow("select count(*) FROM goods g" + whereSql)
	var totalRecords int
	if err := row.Scan(&totalRecords); err != nil {
		return nil, err
	}
	stmt, err := common.DB.Prepare(
		"SELECT g.id, g.`name`, g.priority, g.`status`, g.main_img, g.category_id, g.created_time, c.`name` as category_name from goods g INNER join category c on g.category_id = c.id" + whereSql +
			" LIMIT ?, ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query((pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var gs []model.Goods
	for rows.Next() {
		g := model.Goods{}
		if err := rows.Scan(&g.Id, &g.Name, &g.Priority,
			&g.Status, &g.MainImg, &g.CategoryId, &g.CreatedTimeStr, &g.CategoryName); err != nil {
			return nil, fmt.Errorf("商品数据有误:%s", err.Error())
		}
		gs = append(gs, g)
	}
	return common.NewPage(gs, pageNo, pageSize, totalRecords), nil
}

func ListAllGoodsIdName() ([]model.Goods, error) {

	stmt, err := common.DB.Prepare("SELECT id, `name` FROM goods")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var gs []model.Goods
	for rows.Next() {
		g := model.Goods{}
		if err := rows.Scan(&g.Id, &g.Name); err != nil {
			return nil, fmt.Errorf("商品数据有误:%s", err.Error())
		}
		gs = append(gs, g)
	}
	return gs, nil
}

func GetGoodsById(id int) (*model.Goods, error) {

	sqlTpl := "SELECT g.id, g.`name`, g.description, g.priority, g.`status`, g.main_img, g.brand_id, g.category_id FROM goods g where g.id = ?"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var g model.Goods
	if err = stmt.QueryRow(id).Scan(&g.Id, &g.Name, &g.Description, &g.Priority,
		&g.Status, &g.MainImg, &g.BrandId, &g.CategoryId); err != nil {
		return nil, err
	}
	return &g, nil
}

func GetGoodsAndBCById(goodId int) (*model.Goods, error) {

	sqlTpl := `SELECT g.id, g.name, g.description, g.main_img, g.brand_id, 
       g.category_id, b.name as brand_name, b.logo, b.description as b_desc, c.name as category_name, 
       c.main_img as c_img, c.description as c_desc FROM goods g inner join brand b on g.brand_id = b.id 
           INNER join category c on g.category_id = c.id where g.id = ?`

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var g model.Goods
	if err = stmt.QueryRow(goodId).Scan(&g.Id, &g.Name, &g.Description, &g.MainImg, &g.BrandId,
		&g.CategoryId, &g.BrandName, &g.Logo, &g.BrandDesc,
		&g.CategoryName, &g.CMainImg, &g.CDesc); err != nil {
		return nil, err
	}
	return &g, nil
}

func ListGoodsAttrValueByGoodsId(goodsId int) ([]model.GoodsAttrValue, error) {

	sqlTpl := "select attr_id, `attr_name` FROM goods_attr_value where goods_id = ?"
	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(goodsId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var gs []model.GoodsAttrValue
	for rows.Next() {
		g := model.GoodsAttrValue{}
		if err := rows.Scan(&g.AttrId, &g.AttrName); err != nil {
			return nil, fmt.Errorf("属性值数据有误:%s", err.Error())
		}
		gs = append(gs, g)
	}
	return gs, nil
}

func AddGoods(ctx context.Context, g model.Goods, attrInputs []string, attrValue []model.GoodsAttr, goodsImgs []model.GoodsImg) (int, error) {

	fail := func(err error) (int, error) {
		return 0, fmt.Errorf("AddGoods: %v", err)
	}
	var err error
	tx, err := common.DB.BeginTx(ctx, nil)
	if err != nil {
		return fail(err)
	}
	/**
		Defer the transaction’s rollback. If the transaction succeeds,
	it will be committed before the function exits, making the deferred rollback call a no-op.
		If the transaction fails it won’t be committed,
	meaning that the rollback will be called as the function exits.
	*/
	defer tx.Rollback()

	sqlTpl := `insert into goods(id, name, description, priority, status, main_img, brand_id, category_id, created_time, updated_time)
 		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	result, err := tx.Exec(sqlTpl, g.Id, g.Name, g.Description, g.Priority, 0, g.MainImg, g.BrandId, g.CategoryId,
		time.Now().Add(8*time.Hour), time.Now().Add(8*time.Hour))

	if err != nil {
		return fail(err)
	}
	id, err := result.LastInsertId()

	if err != nil {
		return fail(err)
	}
	// 插入商品属性值
	goodsAttrValue := composeGoodsAttrValueDBParam(attrInputs, int(id), attrValue)
	err = batchAddGodsPropertyVal(ctx, tx, goodsAttrValue)
	if err != nil {
		return fail(err)
	}
	// 详情图片列表
	if len(goodsImgs) > 0 {
		err = batchAddGoodsDetailImg(ctx, tx, goodsImgs, int(id))
		if err != nil {
			return fail(err)
		}
	}
	// 提交事务
	if err = tx.Commit(); err != nil {
		return fail(err)
	}
	return int(id), nil
}

func batchAddGoodsDetailImg(ctx context.Context, tx *sql.Tx, imgs []model.GoodsImg, goodsId int) error {

	sqlTpl := "insert into goods_img(goods_id, img_url, sort) VALUES (?, ?, ?)"

	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	for _, v := range imgs {
		_, err = stmt.ExecContext(ctx, goodsId, v.ImgUrl, v.Sort)
		if err != nil {
			return err
		}
	}
	return nil
}

func composeGoodsAttrValueDBParam(attrInputs []string, id int, attrValue []model.GoodsAttr) []model.GoodsAttrValue {

	var goodsAttrValue []model.GoodsAttrValue
	var gav model.GoodsAttrValue
	var attrId int

	var idNameMap = make(map[int]string, len(attrValue))
	for _, x := range attrValue {
		idNameMap[x.Id] = x.Name
	}

	for _, v := range attrInputs {
		attrId, _ = strconv.Atoi(v)
		gav = model.GoodsAttrValue{GoodsId: id, AttrId: attrId, AttrName: idNameMap[attrId]}
		goodsAttrValue = append(goodsAttrValue, gav)
	}
	return goodsAttrValue
}

func batchAddGodsPropertyVal(ctx context.Context, tx *sql.Tx, value []model.GoodsAttrValue) error {

	sqlTpl := "insert into goods_attr_value(goods_id, attr_id, attr_name) VALUES (?, ?, ?)"

	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	for _, v := range value {
		_, err = stmt.ExecContext(ctx, v.GoodsId, v.AttrId, v.AttrName)
		if err != nil {
			return err
		}
	}
	return nil
}

//func composeBatchSql(tpl string, len int) string {
//
//	if len == 0 {
//		return tpl
//	}
//	if len == 1 {
//		return tpl + " (?, ?, ?, ?)"
//	}
//	sb := strings.Builder{}
//	sb.WriteString(" (?, ?, ?, ?),")
//	for len > 1 {
//		len--
//		sb.WriteString(" (?, ?, ?, ?)")
//	}
//	return sb.String()
//}

func UpdateGoods(ctx context.Context, g model.Goods, attrInputs []string, attrValue []model.GoodsAttr, goodsImgs []model.GoodsImg) (int, error) {

	fail := func(err error) (int, error) {
		return 0, fmt.Errorf("AddGoods: %v", err)
	}
	var err error
	tx, err := common.DB.BeginTx(ctx, nil)
	if err != nil {
		return fail(err)
	}
	defer tx.Rollback()

	sqlTpl := "update goods set `name`=?, description = ?, priority = ?, main_img=?, brand_id = ?, category_id = ?, updated_time=? WHERE id = ?"
	if len(g.MainImg) == 0 {
		sqlTpl = "update goods set `name`=?, description = ?, priority = ?, brand_id = ?, category_id = ?, updated_time=? WHERE id = ?"
	}

	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return fail(err)
	}
	defer stmt.Close()

	var r sql.Result
	if len(g.MainImg) > 0 {
		r, err = stmt.Exec(g.Name, g.Description, g.Priority, g.MainImg, g.BrandId, g.CategoryId, time.Now().Add(8*time.Hour), g.Id)
	} else {
		r, err = stmt.Exec(g.Name, g.Description, g.Priority, g.BrandId, g.CategoryId, time.Now().Add(8*time.Hour), g.Id)
	}

	if err != nil {
		return fail(fmt.Errorf("update goods(id:%d) err:%v", g.Id, err.Error()))
	}
	rowsAffected, err := r.RowsAffected()

	// 更新属性值（先删除再插入）
	err = deleteGoodsAttrValueByGoodsId(ctx, tx, g.Id)
	if err != nil {
		return fail(err)
	}
	goodsAttrValue := composeGoodsAttrValueDBParam(attrInputs, g.Id, attrValue)
	err = batchAddGodsPropertyVal(ctx, tx, goodsAttrValue)
	if err != nil {
		return fail(err)
	}
	if len(goodsImgs) > 0 {
		err = batchDeleteGoodsImg(ctx, tx, g.Id)
		if err != nil {
			return fail(err)
		}
		err = batchAddGoodsDetailImg(ctx, tx, goodsImgs, g.Id)
		if err != nil {
			return fail(err)
		}
	}
	// 提交事务
	if err = tx.Commit(); err != nil {
		return fail(err)
	}

	return int(rowsAffected), err
}

func batchDeleteGoodsImg(ctx context.Context, tx *sql.Tx, goodsId int) error {

	sqlTpl := "delete from goods_img WHERE goods_id = ?"

	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.ExecContext(ctx, goodsId)
	if err != nil {
		return fmt.Errorf("delete good detail img by goodsId:%d, err:%v", goodsId, err.Error())
	}
	return err
}

func deleteGoodsAttrValueByGoodsId(ctx context.Context, tx *sql.Tx, goodsId int) error {

	sqlTpl := "delete from goods_attr_value WHERE goods_id = ?"

	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.ExecContext(ctx, goodsId)
	if err != nil {
		return fmt.Errorf("delete good attr value by goodsId:%d, err:%v", goodsId, err.Error())
	}
	return err
}

func UpdateGoodsStatus(id, status int) error {

	sqlTpl := "update goods set status = ?, updated_time = ? WHERE id = ?"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(status, time.Now().Add(8*time.Hour), id)
	if err != nil {
		return fmt.Errorf("update Goods status, id:%d, err:%v", id, err.Error())
	}
	return err
}

// 下架调用

func UpdateGoodsAndSkuStatus(ctx context.Context, id, status int) error {

	fail := func(err error) error {
		return fmt.Errorf("off sale goods and sku: %v", err)
	}
	var err error
	tx, err := common.DB.BeginTx(ctx, nil)
	if err != nil {
		return fail(err)
	}
	defer tx.Rollback()

	sqlTpl := "update goods set `status` = ?, updated_time=? WHERE id = ?"

	stmt, err := tx.Prepare(sqlTpl)
	if err != nil {
		return fail(err)
	}
	defer stmt.Close()

	_, err = stmt.Exec(status, time.Now().Add(8*time.Hour), id)
	if err != nil {
		return fail(fmt.Errorf("update goods status(id:%d) err:%v", id, err.Error()))
	}
	// 更新相应的sku 状态为 下架
	err = UpdateSkuStatusByGoodsId(ctx, tx, id, status)
	if err != nil {
		return fail(err)
	}
	// 提交事务
	if err = tx.Commit(); err != nil {
		return fail(err)
	}

	return err
}

func ListGoodsImgByGoodsId(goodsId int) ([]model.GoodsImg, error) {

	sqlTpl := "select id, goods_id, img_url FROM goods_img where goods_id = ?"
	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(goodsId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var gi []model.GoodsImg
	for rows.Next() {
		g := model.GoodsImg{}
		if err := rows.Scan(&g.Id, &g.GoodsId, &g.ImgUrl); err != nil {
			return nil, fmt.Errorf("商品图片数据有误:%s", err.Error())
		}
		gi = append(gi, g)
	}
	return gi, nil
}

func composeGoodsSearchQuerySql(name string) string {

	// 没有条件查询
	if len(name) == 0 {
		return ""
	}
	sb := strings.Builder{}
	sb.WriteString(" WHERE g.`name` like '" + strings.TrimSpace(name) + "%'")

	return sb.String()
}

// -----------------app 查询专用开始-----------------------

func ListGoodsByCategoryId(categoryId, status int) ([]int, error) {

	stmt, err := common.DB.Prepare("SELECT id FROM goods where category_id = ? AND `status` = ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(categoryId, status)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var gs []int
	for rows.Next() {
		var goodsId int
		if err := rows.Scan(&goodsId); err != nil {
			return nil, fmt.Errorf("根据类别查询上架商品数据有误:%s", err.Error())
		}
		gs = append(gs, goodsId)
	}
	return gs, nil
}

func ListCategory(goodsIds []int) ([]model.Goods, error) {

	stmt, err := common.DB.Prepare("SELECT g.id, g.category_id, c.`name` as category_name from goods g INNER join category c on g.category_id = c.id where g.id in (?" + strings.Repeat(",?", len(goodsIds)-1) + ") and g.`status` = 1")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	args := make([]interface{}, len(goodsIds))
	for i, id := range goodsIds {
		args[i] = id
	}
	rows, err := stmt.Query(args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var gs []model.Goods
	for rows.Next() {
		var g model.Goods
		if err := rows.Scan(&g.Id, &g.CategoryId, &g.CategoryName); err != nil {
			return nil, fmt.Errorf("ListCategory err:%s", err.Error())
		}
		gs = append(gs, g)
	}
	return gs, nil
}

func PageAppGoods(goodsIdList []int, pageNo, pageSize int) (*common.Page, error) {

	sqlTpl := "select count(*) FROM goods_sku WHERE goods_id in (%d" + strings.Repeat(",%d", len(goodsIdList)-1) + ") AND `status` = ?"
	args := make([]interface{}, len(goodsIdList))
	for i, id := range goodsIdList {
		args[i] = id
	}
	sqlTpl = fmt.Sprintf(sqlTpl, args...)

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	row := stmt.QueryRow(1)
	var totalRecords int
	if err := row.Scan(&totalRecords); err != nil {
		return nil, err
	}
	stmt, err = common.DB.Prepare(fmt.Sprintf("SELECT id, `name`, main_img, real_price FROM goods_sku WHERE goods_id in (%d"+strings.Repeat(",%d", len(goodsIdList)-1)+") AND `status` = ? ORDER BY priority DESC LIMIT ?, ?", args...))
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(1, (pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	for rows.Next() {
		k := model.Sku{}
		if err := rows.Scan(&k.Id, &k.Name, &k.MainImg, &k.RealPrice); err != nil {
			return nil, fmt.Errorf("app 商品sku数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return common.NewPage(ks, pageNo, pageSize, totalRecords), nil
}

func ListAppGoods(goodsIdList []int, pageNo, pageSize int) ([]model.Sku, error) {

	args := make([]interface{}, len(goodsIdList))
	for i, id := range goodsIdList {
		args[i] = id
	}
	stmt, err := common.DB.Prepare(fmt.Sprintf("SELECT id, goods_id, `name`, main_img, real_price FROM goods_sku WHERE goods_id in (%d"+strings.Repeat(",%d", len(goodsIdList)-1)+") AND `status` = 1 ORDER BY priority DESC LIMIT ?, ?", args...))
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query((pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	for rows.Next() {
		k := model.Sku{}
		if err := rows.Scan(&k.Id, &k.GoodsId, &k.Name, &k.MainImg, &k.RealPrice); err != nil {
			return nil, fmt.Errorf("ListAppGoods 商品sku数据有误:%s", err.Error())
		}
		ks = append(ks, k)
	}
	return ks, nil
}

func PageAppGoodsByName(skuName, sort, order string, page, size int, categoryVoList *[]vo.CategoryNav) (*common.Page, error) {

	stmt, err := common.DB.Prepare("select count(*) FROM goods_sku WHERE `name` = ? and `status` = 1")
	if err != nil {
		return nil, err
	}
	row := stmt.QueryRow(skuName)
	var totalRecords int
	if err := row.Scan(&totalRecords); err != nil {
		return nil, err
	}
	var buf strings.Builder
	buf.WriteString("SELECT id, goods_id, `name`, main_img, real_price FROM goods_sku WHERE `name` = ? and `status` = 1 ORDER BY ")
	buf.WriteString(sort)
	buf.WriteString(" ")
	buf.WriteString(order)
	buf.WriteString(" LIMIT ?, ?")
	stmt, err = common.DB.Prepare(buf.String())
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(skuName, (page-1)*size, size)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ks []model.Sku
	var goodsIds []int
	for rows.Next() {
		k := model.Sku{}
		if err := rows.Scan(&k.Id, &k.GoodsId, &k.Name, &k.MainImg, &k.RealPrice); err != nil {
			return nil, fmt.Errorf("app search by sku name->商品sku数据有误:%s", err.Error())
		}
		goodsIds = append(goodsIds, k.GoodsId)
		ks = append(ks, k)
	}
	if len(goodsIds) > 0 {
		categoryList, err := ListCategoryByGoodsIds(goodsIds)
		if err != nil {
			common.Error.Printf("err ListCategoryByGoodsIds by goodsIds:%v, e:%v", goodsIds, err)
		} else {
			for _, v := range categoryList {
				*categoryVoList = append(*categoryVoList, vo.CategoryNav{Id: v.Id, Name: v.Name})
			}
		}
	}
	return common.NewPage(ks, page, size, totalRecords), nil
}

func ListGoodsNameByPriority(limit int) ([]string, error) {

	stmt, err := common.DB.Prepare("SELECT `name` FROM goods WHERE `status` = 1 ORDER BY priority DESC limit ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var gs []string
	for rows.Next() {
		var x string
		if err := rows.Scan(&x); err != nil {
			return nil, fmt.Errorf("hot kw 商品数据有误:%s", err.Error())
		}
		gs = append(gs, x)
	}
	return gs, nil
}
